package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

import model.City;
import model.Country;
import model.Customer;

import util.Number;

public class DBCustomer extends DBPerson implements IFCustomer
{
	private Connection con;

	public DBCustomer()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createCustomer(Customer customer) 
	{
		int rc = -1;
		int insert = super.createPerson(customer);

		if (insert > 0)
		{
			String query = "INSERT INTO customer VALUES(?, ?)";

			if (DBConnection.doDebug())
				DBConnection.printDebug("createCustomer", query);

			try
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, insert);
				ps.setInt(2, customer.getCity().getCityID());

				rc = ps.executeUpdate();

				if (rc > 0)
				{
					rc = insert;
				}

				ps.close();
			}
			catch (Exception e)
			{
				if (DBConnection.doDebug())
				{
					System.out.println("[ERROR] Query exception - createCustomer - INSERT: " + e);
					e.printStackTrace();
				}
			}
		}

		return rc;
	}

	@Override
	public int updateCustomer(Customer customer) 
	{
		int rc = -1;
		int update = super.updatePerson(customer);

		if (update > 0)
		{
			String query = "UPDATE customer SET customer_city_id = ? WHERE customer_id = ?";

			if (DBConnection.doDebug())
				DBConnection.printDebug("updateCustomer", query);

			try 
			{
				PreparedStatement ps = con.prepareStatement(query);

				ps.setInt(1, customer.getCity().getCityID());
				ps.setInt(2, customer.getPersonID());

				rc = ps.executeUpdate();

				ps.close();
			}
			catch (Exception e)
			{
				if (DBConnection.doDebug())
				{
					System.out.println("[ERROR] Query exception - updateCustomer - UPDATE: " + e);
					e.printStackTrace();
				}
			}
		}

		return rc;
	}

	@Override
	public Customer searchCustomerID(int id)
	{
		Customer newCustomer = new Customer();

		String query = "SELECT c.*, p.*, ct.city_name, cn.country_name, cn.country_tld FROM customer AS c LEFT JOIN person AS p ON c.customer_id = p.person_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE p.person_type = '1' AND p.person_id = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchCustomerID", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, id);

			ResultSet results = ps.executeQuery();

			if (results.next())
				newCustomer = createCustomerObject(results, true);

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchCustomerID - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newCustomer;
	}

	@Override
	public Customer searchCustomerByName(String name)
	{
		Customer newCustomer = new Customer();

		String query = "SELECT c.customer_id, c.customer_city_id FROM customer AS c LEFT JOIN person AS p ON c.customer_id = p.person_id WHERE p.person_username = ?";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchCustomerName", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				newCustomer.setPersonID(results.getInt("customer_id"));
				newCustomer.setCity(new City(results.getInt("customer_city_id")));
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchCustomerName - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return newCustomer;
	}

	@Override
	public ArrayList<Customer> getAllCustomers()
	{
		ArrayList<Customer> customerList = new ArrayList<Customer>();

		String query = "SELECT p.person_username, p.person_id FROM person AS p WHERE p.person_type = '1' AND p.person_active = '1' ORDER BY p.person_username ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("getAllCustomers", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Customer newCustomer = new Customer();

				newCustomer.setUsername(results.getString("person_username"));
				newCustomer.setPersonID(results.getInt("person_id"));

				customerList.add(newCustomer);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - getAllCustomers - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return customerList;
	}

	@Override
	public ArrayList<Customer> searchCustomerName(boolean active, String name) 
	{
		return searchCustomers(active, name, "p.person_username LIKE ?", 1);
	}

	@Override
	public ArrayList<Customer> searchCustomerCity(boolean active, String city)
	{
		return searchCustomers(active, city, "ct.city_name LIKE ?", 1);
	}

	@Override
	public ArrayList<Customer> searchCustomerCountry(boolean active, String country)
	{
		return searchCustomers(active, country, "cn.country_name LIKE ?", 1);
	}

	@Override
	public ArrayList<Customer> searchCustomerPhoneNo(boolean active, String phoneNo)
	{
		return searchCustomers(active, phoneNo, "p.person_phoneno LIKE ?", 1);
	}

	@Override
	public ArrayList<Customer> searchCustomerEmail(boolean active, String email)
	{
		return searchCustomers(active, email, "p.person_email LIKE ?", 1);		
	}

	@Override
	public ArrayList<Customer> searchCustomerEverything(boolean active, String search)
	{
		boolean number = Number.isNumber(search);

		return searchCustomers(active, search, "(p.person_username LIKE ? OR p.person_phoneno LIKE ? OR p.person_email LIKE ? OR ct.city_name LIKE ? OR cn.country_name LIKE ?" + (number ? " OR c.customer_id = ?" : "") + " )", (number ? 6 : 5));
	}

	/**
	 * Our method for handling customer search
	 * 
	 * @param 	boolean		are we searching for active only or not
	 * @param	String 		our search word, we use it for the prepared statement
	 * @param 	String		our extra query (in order to keep everything in one method and not 500 lines of code..)
	 * @param 	int			might be kinda hardcoded, number of prepared statements we have to do, most (only) used in searchCustomerEverything
	 * 
	 * @return  ArrayList<Customer>
	 */
	private ArrayList<Customer> searchCustomers(boolean active, String search, String clause, int num) 
	{
		ArrayList<Customer> customerList = new ArrayList<Customer>();

		String query = "SELECT c.*, p.*, ct.city_name, cn.country_name, cn.country_tld FROM customer AS c LEFT JOIN person AS p ON c.customer_id = p.person_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE p.person_type = '1'" + (active ? " AND p.person_active = '1'" : "") + ((clause.length() > 0) ? (" AND " + clause) : "") + " ORDER BY p.person_username ASC";

		if (DBConnection.doDebug())
			DBConnection.printDebug("searchCustomers", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			if (clause.length() > 0)
			{
				ps.setString(1, "%" + search + "%");

				if (num > 1)
				{
					for(int i = 2; i <= num; i++)
						ps.setString(i, (i == 6 ? search : "%" + search + "%"));						
				}
			}

			ResultSet results = ps.executeQuery();

			while(results.next())
			{
				Customer newCustomer = createCustomerObject(results, false);

				customerList.add(newCustomer);
			}

			ps.close();
		}
		catch (Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - searchCustomers - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return customerList;
	}

	private Customer createCustomerObject(ResultSet results, boolean password) throws SQLException
	{
		Customer newCustomer = new Customer();
		createPersonObject(newCustomer, results, password);

		newCustomer.setCity(new City(results.getInt("customer_city_id"), results.getString("city_name"), new Country(results.getString("country_name"), results.getString("country_tld"))));

		return newCustomer;
	}
}